package org.pskovgu.pskovbus;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class PointDataSource {
	private Context context;
	private SQLiteDatabase database;
	  private PointDbOpenHelper dbHelper;
	  private String[] allColumns = { 
			  PointDbOpenHelper.POINT_ID,
			  PointDbOpenHelper.BUS_ID,
			  PointDbOpenHelper.STATION_ID,
			  PointDbOpenHelper.REVERSE,
			  PointDbOpenHelper.TIME,
			  PointDbOpenHelper.WEEKDAYS
	  };

	  public PointDataSource(Context context) {
	    dbHelper = new PointDbOpenHelper(context);
	    this.context = context;
	  }

	  public void open() throws SQLException {
	    database = dbHelper.getWritableDatabase();
	  }

	  public void close() {
	    dbHelper.close();
	  }

	  public Point createPoint(String bus_id, String station_id, int reverse, String time, String weekdays) {
	    ContentValues values = new ContentValues();
	    values.put(PointDbOpenHelper.BUS_ID, bus_id);
	    values.put(PointDbOpenHelper.STATION_ID, station_id);
	    values.put(PointDbOpenHelper.REVERSE, Integer.toString(reverse));
	    values.put(PointDbOpenHelper.TIME, time);
	    values.put(PointDbOpenHelper.WEEKDAYS, weekdays);
	    long insertId = database.insert(PointDbOpenHelper.TABLE_NAME, null, values);
	    Cursor cursor = database.query(PointDbOpenHelper.TABLE_NAME,
	        allColumns, PointDbOpenHelper.BUS_ID + " = " + insertId, null,
	        null, null, null);
	    cursor.moveToFirst();
	    Point newComment = cursorToPoint(cursor);
	    cursor.close();
	    return newComment;
	  }

	  public void deletePoint(Point comment) {
	    long id = comment.getId();
	    System.out.println("Point deleted with id: " + id);
	    database.delete(PointDbOpenHelper.TABLE_NAME, PointDbOpenHelper.POINT_ID
	        + " = " + id, null);
	  }

	  public List<Point> getAllPoints() {
	    List<Point> comments = new ArrayList<Point>();

	    Cursor cursor = database.query(PointDbOpenHelper.TABLE_NAME,
	        allColumns, null, null, null, null, null);

	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      Point comment = cursorToPoint(cursor);
	      comments.add(comment);
	      cursor.moveToNext();
	    }
	    // Make sure to close the cursor
	    cursor.close();
	    return comments;
	  }
	  
	  public List<Point> getPointsById(int id) {
		    List<Point> comments = new ArrayList<Point>();
		    String[] args = {Integer.toString(id)};

		    Cursor cursor = database.query(PointDbOpenHelper.TABLE_NAME,
		        allColumns, PointDbOpenHelper.POINT_ID+" = ?", args, null, null, null);

		    cursor.moveToFirst();
		    while (!cursor.isAfterLast()) {
		      Point comment = cursorToPoint(cursor);
		      comments.add(comment);
		      cursor.moveToNext();
		    }
		    // Make sure to close the cursor
		    cursor.close();
		    return comments;
		  }
	  
	  public List<Point> getPointsByStationId(int id, int direction) {
		    List<Point> comments = new ArrayList<Point>();
		    SimpleDateFormat df = new SimpleDateFormat("HH:mm");
		    
		    String[] args = {Integer.toString(id), df.format(new Date()), Integer.toString(direction)};

		    Cursor cursor = database.query(PointDbOpenHelper.TABLE_NAME,
		        allColumns, PointDbOpenHelper.STATION_ID+" = ? and "+PointDbOpenHelper.TIME+" > ?" + " and "+PointDbOpenHelper.REVERSE+" = ?", args, null, null, PointDbOpenHelper.TIME);
		    
		    cursor.moveToFirst();
		    while (!cursor.isAfterLast()) {
		      Point comment = cursorToPoint(cursor);
		      comments.add(comment);
		      cursor.moveToNext();
		    }
		    // Make sure to close the cursor
		    cursor.close();
		    return comments;
		  }
	  

	  private Point cursorToPoint(Cursor cursor) {
		Point point = new Point();
	    point.setId(cursor.getLong(cursor.getColumnIndex(PointDbOpenHelper.POINT_ID)));
	    BusDataSource busesDS = new BusDataSource(context);        
        busesDS.open();
        int bss = busesDS.getBusesById(cursor.getLong(cursor.getColumnIndex(PointDbOpenHelper.BUS_ID))).size();
        if (bss!=0)
        {
        Bus bus = busesDS.getBusesById(cursor.getLong(cursor.getColumnIndex(PointDbOpenHelper.BUS_ID))).get(0);
        
	    point.setBus(bus);
        }
	    busesDS.close();
	    StationDataSource stationDS = new StationDataSource(context);
	    stationDS.open();
	    Station station = stationDS.getStationsById(cursor.getLong(cursor.getColumnIndex(PointDbOpenHelper.STATION_ID))).get(0);
	    point.setStation(station);
	    stationDS.close();
	    point.setReverse(cursor.getInt(cursor.getColumnIndex(PointDbOpenHelper.REVERSE))!=0);
	    SimpleDateFormat df = new SimpleDateFormat("H:mm");
	    Date time = null;
		try {
			time = df.parse(cursor.getString(cursor.getColumnIndex(PointDbOpenHelper.TIME)));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    point.setTime(time);
	    return point;
	  }

}
